﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Web.UI.WebControls;

using Jumpton.SFA.Entity;
using Jumpton.Persistent;
using System.Data;
using Jumpton.Data;

namespace Jumpton.SFA.Server.BusinessRule
{
    public class PhotoManager
    {
        public PHOTO Load(string LAST_UPDATE, decimal LAST_UPDATE_TIME)
        {
            string where = "LAST_UPDATER='" + LAST_UPDATE + "' and LAST_UPDATE_TIME='" + LAST_UPDATE_TIME + "' ";
            return (PHOTO)ObjectRow.Load(typeof(PHOTO), where);
        }

       
        public PHOTO Load(String ID)
        {
            return (PHOTO)ObjectRow.Load(typeof(PHOTO), string.Format("ID='{0}'", ID));
        }

        public PRODUCTPHOTO LoadProductPhoto(String ID)
        {
            return (PRODUCTPHOTO)ObjectRow.Load(typeof(PRODUCTPHOTO), string.Format("ID='{0}'", ID));
        }

        public ObjectTable LoadImagesByProductID(decimal ProductID)
        {
            return new ObjectTable(typeof(PRODUCTPHOTO), string.Format("PRODUCT_ID={0} and ACTIVE='1' ", ProductID));
        }

        public ObjectTable LoadByDomain(decimal DomainID)
        {
            return new ObjectTable(typeof(PHOTO), string.Format("Domain_ID={0} and ACTIVE='1' ", DomainID), "LAST_UPDATE_TIME DESC");
        }


        public ObjectTable LoadImagesByVisitID(decimal VisitID, string sb)
        {
            if (sb == "")
                sb = " AND PHOTO_TYPE_ID NOT IN(5) ";
            return new ObjectTable(typeof(PHOTO), string.Format("VISIT_ID={0}  " + sb, VisitID));
        }

        public PHOTO LoadByVisitID(decimal VisitID)
        {
            return (PHOTO)ObjectRow.Load(typeof(PHOTO), string.Format("VISIT_ID={0}", VisitID));
        }

        public bool SaveProductPhoto(decimal ProductID, String PhotoIDs, decimal DomainID)
        {
            return SqlData.ExecuteSP("SP_UPDATE_PRODUCT_PHOTO", ProductID, PhotoIDs, DomainID)>0;
        }

        public bool Save(PHOTO photo)
        {
            if(photo != null)
            {

                photo.Save();
                return true;
            }
            else
                return false;
        }

        public bool DeleteProductPhoto(String ProductPhotoID)
        {
            SqlData.ExecuteSql("DELETE PRODUCT_PHOTO WHERE ID=" + ProductPhotoID);
            return true;
        }

        public bool DeletePhoto(String PhotoID)
        {
            SqlData.ExecuteSql("DELETE PHOTO WHERE ID=" + PhotoID);
            return true;
        }


        public String SavePhoto(PHOTO photo)
        {
            return this.Save(
                photo.USERID
                ,photo["VISITID"] !=null ? photo.VISITID.ToString() : null
                , photo["PHOTOTYPEID"] != null ? photo.PHOTOTYPEID.ToString() : null
                , photo.PHOTOPATH
                , photo.PHOTODATE
                , photo.DESCRIPTION
                , photo.DOMAINID
                , photo.LAST_UPDATER
                , photo.LAST_UPDATE_TIME);
        }
        private String Save(decimal USER_ID, String VISIT_ID, String PHOTO_TYPE_ID, string PHOTO_PATH, decimal PHOTO_DATE, string DESCRIPTION, decimal DOMAIN_ID, string LAST_UPDATER, decimal LAST_UPDATE_TIME)
        {
            String id = BusinessLogic.GetDateTimeString(DateTime.Now) + new Random().Next(1000, 9000);
            string sql = "INSERT INTO PHOTO (ID,USER_ID, VISIT_ID, PHOTO_TYPE_ID,PHOTO_PATH, PHOTO_DATE, DESCRIPTION, ACTIVE, DOMAIN_ID, LAST_UPDATER, LAST_UPDATE_TIME) ";
            sql += " VALUES({10},{0},{1},{2},'{3}',{4},'{5}',{6},{7},'{8}',{9})";
            sql = string.Format(sql, USER_ID, VISIT_ID == null ? "null" : VISIT_ID, PHOTO_TYPE_ID == null ? "null" : PHOTO_TYPE_ID, PHOTO_PATH, PHOTO_DATE, DESCRIPTION, 1, DOMAIN_ID, LAST_UPDATER, LAST_UPDATE_TIME, id);

            
            SqlData.ExecuteSql(sql);
            return id;
            
        }

      

        public static DataSet GetPhotoList(decimal domainId, string where)
        {
            string strSql = string.Format(@"select P.ID,P.PHOTO_PATH,P.PHOTO_TYPE_ID,P.USER_ID,V.START_TIME,C.NAME,C.CUST_CLASS_CODE FROM PHOTO P 
                            LEFT JOIN VISITS V ON V.ID=P.VISIT_ID
                            LEFT JOIN CUSTOMERS C ON C.ID=V.CUSTOMER_ID
                            WHERE C.DOMAIN_ID={0} AND P.PHOTO_TYPE_ID NOT IN(5) {1}", domainId, where);

            DataSet ds = SqlData.OpenSql(strSql);
            return ds;

        }

        /// <summary>
        /// 分页提取相关数据
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="strGetFields"></param>
        /// <returns></returns>
        public static DataSet GetByPage(decimal domainId, decimal start, decimal end, string where)
        {

            string sql = @"WITH TEMPPHOTOS AS              
                            (select row_number() over(order by V.START_TIME desc) as row, P.ID,P.PHOTO_PATH,P.PHOTO_TYPE_ID,P.USER_ID,V.START_TIME,C.NAME,C.CUST_CLASS_CODE FROM PHOTO P 
                            LEFT JOIN VISITS V ON V.ID=P.VISIT_ID
                            LEFT JOIN CUSTOMERS C ON C.ID=V.CUSTOMER_ID
                            WHERE C.DOMAIN_ID='{0}' AND P.PHOTO_TYPE_ID NOT IN(5) {1})
                            SELECT * from TEMPPHOTOS WHERE ROW BETWEEN {2} AND {3}";

            sql = string.Format(sql, domainId, where, start, end);
            return SqlData.OpenSql(string.Format(sql, start, end));
        }

        /// <summary>
        /// 分页提取相关数据
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="strGetFields"></param>
        /// <returns></returns>
        public static DataSet GetPhotoByPage(decimal domainId, decimal start, decimal end, string where)
        {

            string sql = @"WITH TEMPPHOTOS AS              
                            (select row_number() over(order by P.LAST_UPDATE_TIME desc) as row, P.* FROM PHOTO P 
                           WHERE P.DOMAIN_ID='{0}'  {1})
                            SELECT * from TEMPPHOTOS WHERE ROW BETWEEN {2} AND {3}";

            sql = string.Format(sql, domainId, where, start, end);
            return SqlData.OpenSql(sql);
        }

        //        SELECT p.PHOTO_PATH,cpr.CODE as photcode,b.NAME AS brandName,c.NAME AS customerName,c.CODE AS customerCode,v.START_TIME,v.STOP_TIME,m.CONTENDER
        //FROM VISITS AS v LEFT JOIN
        //(SELECT data.BRAND_ID,data.CUSTOMERS_ID,config.CODE 
        // FROM CPR_BRAND_DATAS AS data INNER JOIN CPR_BRAND_CONFIG AS config ON
        //data.BRAND_CONFIG_ID=config.ID) AS cpr    --PHOTO AS p ON p.VISIT_ID =v.ID 
        // ON V.CUSTOMER_ID=cpr .CUSTOMERS_ID LEFT JOIN
        //CUSTOMERS AS c ON V.CUSTOMER_ID =c.ID LEFT JOIN 
        //PHOTO AS p ON P.VISIT_ID=v.ID LEFT JOIN
        //BRANDS AS b ON cpr.BRAND_ID =b.ID INNER JOIN MANUFACTURERS AS m ON B.MANUFACTURER_CODE=M.CODE 
        // WHERE c.DOMAIN_ID ='26' and cpr .CODE in ('PHOTO1','PHOTO2','PHOTO3','PHOTO4')
        //ORDER BY customerName,START_TIME
        public DataSet GetPhotos(decimal domainID, string where)
        {
            string sql = @"select temp.*,CPR.* from 
                            (select V.ID,V.Note,C.NAME as customerName,V.START_TIME,V.STOP_TIME
                            from VISITS V,CUSTOMERS C where C.ID = V.CUSTOMER_ID and V.DOMAIN_ID={0}
                             {1} ) temp inner join (
                             select data.VISITID,data.PHOTO_ID,p.PHOTO_PATH,config.MEASUREMENTNAME,b.NAME as brandName,M.CONTENDER
                             from 
                              CPR_FORM_DATAS data inner join  CPR_FORM_CONFIG config on data.FORM_CONFIG_ID=config.ID
                              inner join FORMS f on f.ID=data.FORM_ID and f.DOMAIN_ID={0}
                              inner join BRANDS b on b.CODE=f.BRAND_CODE and b.DOMAIN_ID={0} and b.ACTIVE = 1
                              INNER JOIN MANUFACTURERS M ON b.MANUFACTURER_CODE = M.CODE AND M.DOMAIN_ID = {0} 
                              inner join PHOTO p on data.PHOTO_ID= p.ID and p.DOMAIN_ID={0}
                              and config.CODE in ('PHOTO1','PHOTO2','PHOTO3','PHOTO4')
                            ) CPR
                            on CPR.VISITID = temp.ID ORDER BY START_TIME, customerName";
            sql = string.Format(sql, domainID, where);
            return SqlData.OpenSql(sql);
        }



        public DataSet GetPhotos(decimal domainID, string where, string userids)
        {
            string sql = @"select temp.*,CPR.* from 
                            (select V.ID,V.Note,C.NAME as customerName,V.START_TIME,V.STOP_TIME
                            from VISITS V,CUSTOMERS C,USERS U where C.ID = V.CUSTOMER_ID and V.DOMAIN_ID={0}
                             {1} AND  V.USER_ID = U.ID AND U.ID IN ({2}) ) temp inner join (
                             select data.VISITID,data.PHOTO_ID,p.PHOTO_PATH,config.MEASUREMENTNAME,b.NAME as brandName,M.CONTENDER
                             from 
                              CPR_FORM_DATAS data inner join  CPR_FORM_CONFIG config on data.FORM_CONFIG_ID=config.ID
                              inner join FORMS f on f.ID=data.FORM_ID and f.DOMAIN_ID={0}
                              inner join BRANDS b on b.CODE=f.BRAND_CODE and b.DOMAIN_ID={0} and b.ACTIVE = 1
                              INNER JOIN MANUFACTURERS M ON b.MANUFACTURER_CODE = M.CODE AND M.DOMAIN_ID = {0} 
                              inner join PHOTO p on data.PHOTO_ID= p.ID and p.DOMAIN_ID={0}
                              and config.CODE in ('PHOTO1','PHOTO2','PHOTO3','PHOTO4')
                            ) CPR
                            on CPR.VISITID = temp.ID ORDER BY START_TIME, customerName";
            sql = string.Format(sql, domainID, where, userids);
            return SqlData.OpenSql(sql);
        }

        /// <summary>
        /// 根据条件获得记录数
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public static decimal GetCount(decimal domainId, string strwhere)
        {
            string strSql = string.Format(@"select count(1) FROM PHOTO P 
                            LEFT JOIN VISITS V ON V.ID=P.VISIT_ID
                            LEFT JOIN CUSTOMERS C ON C.ID=V.CUSTOMER_ID
                            WHERE C.DOMAIN_ID={0} AND P.PHOTO_TYPE_ID NOT IN(5) {1}", domainId, strwhere);

            decimal tmp = -1;
            decimal.TryParse(SqlData.ExecuteScalar(strSql).ToString(), out tmp);

            return tmp;
        }

        /// <summary>
        /// 根据条件获得记录数
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public static decimal Count(decimal domainId, string strwhere)
        {
            string strSql = string.Format(@"select count(*) FROM PHOTO P 
                            WHERE P.DOMAIN_ID={0} {1}", domainId, strwhere);

            decimal tmp = -1;
            decimal.TryParse(SqlData.ExecuteScalar(strSql).ToString(), out tmp);

            return tmp;
        }
    }
}
